#!/bin/bash

if [ -n "$1" ] ; then
  data_date=$1
else
  data_date=`date -d '-1 days' +%F`
fi



DIM_ADS_PLATFORM_INFO_FULL_MYSQL="
USE jtp_warehouse;
WITH t1 AS (
    SELECT
        id, ad_id, platform_id, create_time, cancel_time
    FROM jtp_warehouse.ods_ads_platform_full
    WHERE dt = '${data_date}'
) , t2 AS (
    SELECT
        id, product_id, material_id, group_id, ad_name
    FROM jtp_warehouse.ods_ads_log_full
    WHERE dt = '${data_date}'
) , t3 AS (
    SELECT
        id, name, price
    FROM jtp_warehouse.ods_product_full
    WHERE dt = '${data_date}'
) , t4 AS (
    SELECT
        id, platform, platform_alias_zh
    FROM jtp_warehouse.ods_platform_info_full
    WHERE dt = '${data_date}'
)
INSERT OVERWRITE TABLE jtp_warehouse.dim_ads_platform_info_full PARTITION (dt = '${data_date}')
SELECT
    t1.id
    ,t1.ad_id
    ,t2.ad_name
    ,t2.group_id
    ,t2.product_id
    ,t3.name AS product_name
    ,t3.price AS product_price
    ,t2.material_id
    ,t1.platform_id
    ,t4.platform AS platform_name
    ,t4.platform_alias_zh AS platform_name_zh
    ,t1.create_time
    ,t1.cancel_time
FROM t1
    LEFT JOIN t2 ON t1.ad_id = t2.id
    LEFT JOIN t3 ON t2.product_id = t3.id
    LEFT JOIN t4 ON t1.platform_id = t4.id
;
"

/opt/module/spark/bin/beeline -u jdbc:hive2://node101:10001 -n bwie -e  "${DIM_ADS_PLATFORM_INFO_FULL_MYSQL}"